package com.dragon.codefx.utils;

import com.dragon.codefx.dataBase.entity.DatabaseConfigEntity;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

import static com.dragon.codefx.utils.JdbcUtil.identifyDatabaseType;


/**
 * 数据库查询工具
 * @author lsc
 * @since 2025/6/20 16:09
 **/
public class SchemaUtils {
    @SneakyThrows
    public static List<Map<String, Object>> schemaList(DatabaseConfigEntity entity) {
        // 参数校验
        if (StringUtils.isEmpty(entity.getUrl())) {
            throw new IllegalArgumentException("数据库连接URL不能为空");
        }
        // 获取数据库连接信息
        String jdbcUrl = buildJdbcUrlWithDefaultParams(entity.getUrl(), entity.getPort(), entity.getType(), null);
        // 根据数据库类型获取对应的Schema查询SQL
        String schemaQuerySql = getSchemaQuerySql(jdbcUrl);
        // 执行查询并返回结果
        return executeSchemaQuery(schemaQuerySql, jdbcUrl, entity.getUsername(), entity.getPassword());
    }

    /**
     * 为特定数据库类型添加推荐的默认参数
     */
    public static String buildJdbcUrlWithDefaultParams(String host, String port, String type, String databaseName) {
        Map<String, String> defaultParams = new HashMap<>();

        switch (type.toLowerCase()) {
            case "mysql":
                defaultParams.put("useSSL", "false");
                defaultParams.put("serverTimezone", "UTC");
                defaultParams.put("characterEncoding", "UTF-8");
                break;
            case "oracle":
                defaultParams.put("oracle.jdbc.timezoneAsRegion", "false");
                break;
            case "sqlserver":
                defaultParams.put("encrypt", "false");
                defaultParams.put("trustServerCertificate", "true");
                break;
            case "postgresql":
                defaultParams.put("ssl", "false");
                break;
        }

        return buildJdbcUrl(host, port, databaseName, type, defaultParams);
    }

    /**
     * 根据数据库类型和参数构建完整的JDBC URL
     *
     * @param host 数据库主机地址
     * @param port 数据库端口号
     * @param databaseName 数据库名称
     * @param type 数据库类型(mysql/oracle/sqlserver/postgresql/db2/sqlite)
     * @param params 额外的连接参数(可选)
     * @return 完整的JDBC连接URL
     * @throws IllegalArgumentException 如果参数不合法或数据库类型不支持
     */
    public static String buildJdbcUrl(String host, String port, String databaseName,
                                      String type, Map<String, String> params) {
        // 参数校验
        if (StringUtils.isEmpty(host)) {
            throw new IllegalArgumentException("数据库主机地址不能为空");
        }

        // 构建基础URL
        String baseUrl;
        switch (type.toLowerCase()) {
            case "mysql":
                baseUrl = String.format("jdbc:mysql://%s:%s%s",
                        host, port,
                        StringUtils.isEmpty(databaseName) ? "" : "/" + databaseName);
                break;
            case "oracle":
                if (StringUtils.isEmpty(databaseName)) {
                    // 使用服务名格式，需要额外的服务名参数
                    throw new IllegalArgumentException("Oracle需要指定SID或服务名");
                }
                baseUrl = String.format("jdbc:oracle:thin:@%s:%s:%s",
                        host, port, databaseName);
                break;
            case "sqlserver":
                baseUrl = String.format("jdbc:sqlserver://%s:%s%s",
                        host, port,
                        StringUtils.isEmpty(databaseName) ? "" : ";databaseName=" + databaseName);
                break;
            case "postgresql":
                baseUrl = String.format("jdbc:postgresql://%s:%s%s",
                        host, port,
                        StringUtils.isEmpty(databaseName) ? "" : "/" + databaseName);
                break;
            case "db2":
                if (StringUtils.isEmpty(databaseName)) {
                    throw new IllegalArgumentException("DB2需要指定数据库名");
                }
                baseUrl = String.format("jdbc:db2://%s:%s/%s",
                        host, port, databaseName);
                break;
            case "sqlite":
                return String.format("jdbc:sqlite:%s", host); // SQLite使用文件路径
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + type);
        }

        // 添加额外参数
        if (params != null && !params.isEmpty()) {
            String queryString = params.entrySet().stream()
                    .map(entry -> entry.getKey() + "=" + entry.getValue())
                    .collect(Collectors.joining("&"));

            baseUrl += (baseUrl.contains("?") ? "&" : "?") + queryString;
        }
        return baseUrl;
    }


    /**
     * 根据JDBC URL识别数据库类型并返回对应的Schema查询SQL
     */
    private static String getSchemaQuerySql(String jdbcUrl) {
        // 识别数据库类型
        JdbcUtil.DatabaseType type = identifyDatabaseType(jdbcUrl);
        // 返回对应数据库的Schema查询语句
        switch (type) {
            case MYSQL:
                return "SELECT * FROM INFORMATION_SCHEMA.SCHEMATA " +
                        "WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')";
//                return "SELECT SCHEMA_NAME AS schema_name FROM INFORMATION_SCHEMA.SCHEMATA " +
//                        "WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')";
            case ORACLE:
                return "SELECT USERNAME AS schema_name FROM ALL_USERS " +
                        "WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')";
            case SQLSERVER:
                return "SELECT name AS schema_name FROM sys.databases " +
                        "WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')";
            case POSTGRESQL:
                return "SELECT schema_name FROM information_schema.schemata " +
                        "WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema'";
            case DB2:
                return "SELECT SCHEMANAME AS schema_name FROM SYSCAT.SCHEMATA";
            default:
                throw new UnsupportedOperationException("不支持的数据库类型: " + type);
        }
    }

    /**
     * 执行Schema查询并返回结果
     */
    @SneakyThrows
    private static List<Map<String, Object>> executeSchemaQuery(String sql, String jdbcUrl,
                                                                String username, String password) {
        try (Connection connection = ConnectionManager.getConnection(jdbcUrl, username, password); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) {
            // 处理结果集
            List<Map<String, Object>> result = new ArrayList<>();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(i);
                    row.put(columnName, value);
                }
                result.add(row);
            }
            return result;
        }
    }
}
